--Extrae el numero para el siguiente codigo en la tabla
-- Function: sp_get_id_cliente()
-- DROP FUNCTION sp_get_id_cliente();

--GET ID DEL CLIENTE
CREATE OR REPLACE FUNCTION sp_get_id_cliente(OUT id integer)
  RETURNS integer AS
$BODY$ 
 BEGIN
	id := (select COALESCE(max("numIDCliente"),0) from public."Clientes");
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION sp_get_id_cliente()
  OWNER TO postgres;

--GET ID DEL PROVEEDOR
CREATE OR REPLACE FUNCTION sp_get_id_proveedor(OUT id integer)
  RETURNS integer AS
$BODY$ 
 BEGIN
	id := (select COALESCE(max("numIDProveedor"),0) from public."Proveedores");
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION sp_get_id_proveedor()
  OWNER TO postgres;

--GET ID DEL EMPLEADO
CREATE OR REPLACE FUNCTION sp_get_id_empleado(OUT id integer)
  RETURNS integer AS
$BODY$ 
 BEGIN
	id := (select COALESCE(max("numIDEmpleado"),0) from public."Empleados");
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION sp_get_id_empleado()
  OWNER TO postgres;

 -- CATEGORIA
 CREATE OR REPLACE FUNCTION sp_get_id_categoria(OUT id integer)
  RETURNS integer AS
$BODY$ 
 BEGIN
	id := (select COALESCE(max("numIDCategoria"),0) from public."Categorias");
END;
$BODY$
  LANGUAGE plpgsql VOLATILE;
  
--PRODUCTO
 CREATE OR REPLACE FUNCTION sp_get_id_producto(OUT id integer)
  RETURNS integer AS
$BODY$ 
 BEGIN
	id := (select COALESCE(max("numIDProducto"),0) from public."Productos");
END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

--FACTURA NUMERO
 CREATE OR REPLACE FUNCTION sp_get_numfactura(OUT res integer, IN par integer)
  RETURNS integer AS
$BODY$ 
 BEGIN
	res := (select "numIDFactura" from public."Facturas" where "numIDFactura" = par);
END;
$BODY$
  LANGUAGE plpgsql VOLATILE;
  -- OBTENER ROL DEL EMPLEADO
CREATE OR REPLACE FUNCTION sp_get_rol_empleado(IN usuario character varying, IN passwd character varying, OUT rol character varying)
  RETURNS character varying AS
$BODY$ 
 BEGIN
	rol := (select "txtRol" 
	from public."EMPLEADO_USERS" 
	where "txtUser" = usuario and "txtPasswd" = passwd);
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION sp_get_rol_empleado(character varying, character varying)
  OWNER TO postgres;  